Skip Headers

Oracle® Database JDBC Developer's Guide and Reference 10g Release 2 (10.2) Part Number B14355-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 1 Introducing JDBC

This chapter provides an overview of the Oracle implementation of Java Database Connectivity (JDBC), covering the following topics:

- Overview of JDBC

- Overview of the Oracle JDBC Drivers

- Overview of Application and Applet Functionality

- Server-Side Basics

- Environments and Support

- Changes At This Release

## Overview of JDBC

JDBC is a Java standard that provides the interface for connecting from Java to relational databases. The JDBC standard is defined by Sun Microsystems and implemented through the standard `java.sql` interfaces. This allows individual providers to implement and extend the standard with their own JDBC drivers.

JDBC is based on the X/Open SQL Call Level Interface (CLI) and complies with the SQL92 Entry Level standard.

## Overview of the Oracle JDBC Drivers

In addition to supporting the standard JDBC application programming interfaces (APIs), Oracle drivers have extensions to support Oracle-specific data types and to enhance performance.

Oracle provides the following JDBC drivers:

- Thin driver

  It is a pure Java driver used on the client side, without an Oracle client installation. It can be used with both applets and applications.

- Oracle Call Interface (OCI) driver

  It is used on the client-side with an Oracle client installation. It can be used only with applications.

- Server-side Thin driver

  It is functionally similar to the client-side Thin driver. However, it is used for code that runs on the database server and needs to access another session either on the same server or on a remote server on any tier.

- Server-side internal driver

  It is used for code that runs on the database server and accesses the same session. That is, the code runs and accesses data from a single Oracle session.

Figure 1-1 illustrates the architecture of the Oracle JDBC drivers and the Oracle Database.

Figure 1-1 Architecture of the Oracle JDBC Drivers and Oracle Database

Description of "Figure 1-1 Architecture of the Oracle JDBC Drivers and Oracle Database"

This section covers the following topics:

- Common Features of Oracle JDBC Drivers

- JDBC Thin Driver

- JDBC OCI Driver

- JDBC Server-Side Thin Driver

- JDBC Server-Side Internal Driver

- Choosing the Appropriate Driver

### Common Features of Oracle JDBC Drivers

The server-side and client-side Oracle JDBC drivers provide the same basic functionality.

The JDBC Thin and OCI drivers support the following versions of Java Development Kit (JDK): 1.2.x, 1.3.x and 1.4.x. The server-side Thin driver and server-side internal driver support JDK 1.4.1. All the JDBC drivers support the following standards and features:

- Same syntax and APIs

- Same Oracle extensions

- Full support for multithreaded applications

Oracle JDBC drivers implement the standard `java.sql` interfaces. You can access the Oracle-specific features, in addition to the standard features, by using the `oracle.jdbc` package.

### JDBC Thin Driver

The JDBC Thin driver is a pure Java, Type IV driver that can be used in applications and applets. It is platform-independent and does not require any additional Oracle software on the client side. The JDBC Thin driver communicates with the server using SQL*Net to access the Oracle Database.

The JDBC Thin driver allows a direct connection to the database by providing an implementation of SQL*Net on top of Java sockets. The driver supports the TCP/IP protocol and requires a TNS listener on the TCP/IP sockets on the database server.

See Also: Chapter 6, "Features Specific to JDBC Thin"

### JDBC OCI Driver

The JDBC OCI driver is a Type II driver used with Java applications. It requires an Oracle client installation and, therefore, is Oracle platform-specific. It supports all installed Oracle Net adapters, including IPC, named pipes, TCP/IP, and Internetwork Packet Exchange/Sequenced Packet Exchange (IPX/SPX).

The JDBC OCI driver, written in a combination of Java and C, converts JDBC invocations to calls to OCI, using native methods to call C-entry points. These calls communicate with the database using SQL*Net.

The JDBC OCI driver uses the OCI libraries, C-entry points, Oracle Net, core libraries, and other necessary files on the client computer where it is installed.

OCI is an API that enables you to create applications that use the native procedures or function calls of a third-generation language to access Oracle Database and control all phases of the SQL statement processing.

See Also: Chapter 7, "Features Specific to JDBC OCI"

### JDBC Server-Side Thin Driver

The JDBC server-side Thin driver offers the same functionality as the JDBC Thin driver that runs on the client side. However, the JDBC server-side Thin driven runs inside the Oracle Database and accesses a remote database or a different session on the same database.

This driver is useful in the following scenarios:

- Accessing a remote database server from an Oracle Database instance acting as a middle tier

- Accessing an Oracle Database session from inside another, such as from a Java stored procedure

The use of JDBC Thin driver from a client application or from inside a server does not affect the code.

See Also: Chapter 6, "Features Specific to JDBC Thin"

Permission for the Server-Side Thin Driver

The JDBC server-side Thin driver opens a socket for its connection to the database. Because Oracle Database enforces the Java security model, a check is performed for a `SocketPermission` object.

To use the JDBC server-side Thin driver, the connecting user must be granted the appropriate permission. The following is an example of how the permission can be granted for the user `SCOTT`:

    CREATE ROLE jdbcthin;
    CALL dbms_java.grant_permission('JDBCTHIN', 'java.net.SocketPermission', '*', 'connect');
    GRANT jdbcthin TO SCOTT;

Note that `JDBCTHIN` in the `grant_permission` call must be in uppercase. The asterisk (`*`) is a pattern. You can restrict the user by granting permission to connect to only specific computers or ports.

See Also: Oracle Database Java Developer's Guide

### JDBC Server-Side Internal Driver

The JDBC server-side internal driver supports any Java code that runs inside the Oracle Database, such as in a Java stored procedures or Enterprise JavaBean (EJB), and must access the same database. It lets the Java virtual machine (JVM) to communicate directly with the SQL engine. The driver supports only JDK 1.4.1.

The JDBC server-side internal driver, the Oracle JVM, the database, and the SQL engine all run within the same address space, and therefore, the issue of network round trips is irrelevant. The programs access the SQL engine by using function calls.

Note: The server-side internal driver does not support the `cancel` and `setQueryTimeout` methods of the `Statement` class.

The JDBC server-side internal driver is fully consistent with the client-side drivers and supports the same features and extensions.

See Also: Chapter 8, "Server-Side Internal Driver"

### Choosing the Appropriate Driver

Consider the following when choosing a JDBC driver for your application or applet:

- In general, unless you need OCI-specific features, such as support for non-TCP/IP networks, use the JDBC Thin driver.

- If you want maximum portability and performance, then use the JDBC Thin driver. You can connect to the Oracle Database from either an application or an applet using the JDBC Thin driver.

- If you want to use Lightweight Directory Access Protocol (LDAP) over Secure Sockets Layer (SSL), then use the JDBC Thin driver.

- If you are writing a client application for an Oracle client environment and need OCI-driver-specific features, such as support for non-TCP/IP networks, then use the JDBC OCI driver.

- If you are writing an applet, then you must use the JDBC Thin driver.

- For code that runs in the database server acting as a middle tier, use the JDBC server-side Thin driver.

- If your code will run inside the target database server, then use the JDBC server-side internal driver to access that server.

### Feature Differences Between JDBC OCI and Thin Drivers

Table 1-1 lists the features that are specific either to the JDBC OCI or JDBC Thin driver in Oracle Database 10g release 2 (10.2).

Table 1-1 Feature Differences Between JDBC OCI and JDBC Thin Drivers

JDBC OCI Driver | JDBC Thin Driver
--- | ---
OCI connection pooling | Support for applets
OCI optimized fetch | Default support for Native XA
Client-side object cache |
Transparent Application Failover (TAF) |
OCI Instant Client |
Instant Client Light (English) |
Strong authentication; Kerberos, PKI certificates |

Notes: The OCI optimized fetch and client-side object cache features are internal to the JDBC OCI driver and are not applicable to the JDBC Thin driver. Most JDBC OCI driver features are not available in the JDBC Thin driver because they are inherited from OCI.

See Also: Chapter 6, "Features Specific to JDBC Thin" Chapter 7, "Features Specific to JDBC OCI"

## Overview of Application and Applet Functionality

This section compares and contrasts the basic functionality of JDBC applications and applets. It also introduces the Oracle extensions that can be used by application and applet programmers. This sections covers the following topics:

- Applet Basics

- Oracle Extensions

### Applet Basics

You can use only the Oracle JDBC Thin driver for an applet.

See Also: "JDBC in Applets"

Applets and Security

An applet can open network connections only to the host computer from which it was downloaded. Therefore, an applet can connect only to databases on the originating computer. If you want your applet to connect to a database running on a different computer, then you have the following options:

- Use the Oracle Connection Manager on the host computer. The applet can connect to the Connection Manager, which connects to a database on another computer.

- Use signed applets, which can request socket connection privileges to other computers.

Your applet can take advantage of the data encryption and integrity checksum features of the Oracle Advanced Security option.

Applets and Firewalls

An applet can connect to a database through a firewall.

See Also: "Using Applets with Firewalls"

Packaging and Deploying Applets

To package and deploy an applet, you must place the JDBC Thin driver classes and the applet classes in the same `.zip` file.

See Also: "Packaging Applets"

### Oracle Extensions

A number of Oracle extensions are available, which can be used by Oracle JDBC application and applet programmers. These extensions include:

- Type extensions, such as `ROWID` and `REF CURSOR` types

- Wrapper classes for SQL types provided by the `oracle.sql` package

- Support for custom Java classes to map to user-defined types

- Extended large object (LOB) support

- Extended connection, statement, and result set functionality

- Performance enhancements

See Also: Chapter 5, "Oracle Extensions" and Chapter 25, "Performance Extensions"

## Server-Side Basics

By using the JDBC server-side internal driver, code that runs in the Oracle Database, such as Java stored procedures or EJBs, can access the database in which it runs.

See Also: Chapter 8, "Server-Side Internal Driver"

Session and Transaction Context

The JDBC server-side internal driver operates within a default session and default transaction context.

See Also: "Session and Transaction Context".

Connecting to the Database

The JDBC server-side internal driver uses a default connection to the database. You connect to the database with the `OracleDataSource.getconnection` method.

See Also: "Connecting to the Database".

## Environments and Support

This section provides a brief discussion of the following topics:

- Supported JDK and JDBC Versions

- JNI and Java Environments

- JDBC and IDEs

### Supported JDK and JDBC Versions

In Oracle Database 10g release 2 (10.2), all the JDBC drivers are compatible with JDK 1.2.x and later. JDK 1.0.x and JDK 1.1.x are no longer supported, and therefore, the `classes111.zip`, `classes111.jar`, `classes111_g.zip`, `classes111_g.jar`, and `nls_charset11.zip` files are no longer provided.

See Also: "Compatibilities for Oracle JDBC Drivers"

### JNI and Java Environments

The JDBC OCI driver uses the standard Java Native Interface (JNI) to call OCI C libraries. You can use the JDBC OCI driver with JVMs other than that of Sun Microsystems, in particular, with Microsoft and IBM JVMs.

### JDBC and IDEs

The Oracle JDeveloper Suite provides developers with a single, integrated set of products to build, debug, and deploy component-based database applications for the Internet. The Oracle JDeveloper environment contains integrated support for JDBC, including the JDBC Thin driver and the native OCI driver. The database component of Oracle JDeveloper uses the JDBC drivers to manage the connection between the application running on the client and the server.

## Changes At This Release

The Oracle implementation JDBC provides many enhancements in Oracle Database 10g. This section gives an overview of these enhancements. It is divided into the following sections:

- New Features

- Desupported Features

- Interface Changes

### New Features

Oracle Database 10g release 2 (10.2) supports the following new features:

- DML returning

  The JDBC OCI and the JDBC Thin drivers support the data manipulation language (DML) returning feature. DML returning enables you to retrieve auto-generated keys along with other columns or values that your application may use.

  See Also: "DML Returning"

- JSR 114 RowSets

  Oracle Database 10g release 2 (10.2) provides support for all the RowSet implementations defined in the JDBC RowSet Implementations Specification (JSR-114). In particular, Oracle Database 10g release 2 (10.2) provides support for WebRowSet, FilteredRowSet, and JoinRowSet.

  See Also: Chapter 20, "JDBC RowSets"

- `NCHAR` literal support

  Oracle Database 10g release 2 (10.2) provides the NCHAR literal support. For further information, refer to the Note in "NCHAR, NVARCHAR2, NCLOB and the defaultNChar Property".

- Proxy authentication

  In this release of Oracle Database, the proxy authentication feature is supported by both the JDBC Thin and JDBC OCI drivers.

  See Also: Chapter 12, "Proxy Authentication"

- Result set holdability

  Oracle JDBC drivers provide support for result set holdability, which is a feature of the JDBC 3.0 standard. This feature enables applications to decide whether the `ResultSet` objects should be open or closed, when an implicit or explicit commit operation is performed.

  See Also: "Result Set Holdability"

- Retrieval of auto-generated keys

  Oracle Database 10g release 2 (10.2) provides support for the retrieval of auto-generated keys JDBC 3.0 standard feature. This feature enables you to retrieve values that are generated by the database.

  See Also: "Retrieval of Auto-Generated Keys"

- Run-time connection load balancing

  The Oracle JDBC Thin and JDBC OCI drivers support the run-time connection load balancing feature. This feature enables routing of work requests to a database instance that offers the best performance, minimizing the need to relocate work.

  See Also: Chapter 24, " Run-Time Connection Load Balancing"

- SSL support

  Oracle Database 10g release 2 (10.2) provides support for the Secure Sockets Layer (SSL) Protocol.

  See Also: Chapter 11, "SSL Support"

- XAConnection caching

  The implicit connection caching feature supports the caching of XA connections. This feature is supported by both the JDBC Thin and JDBC OCI drivers.

  See Also: Chapter 23, "Implicit Connection Caching"

### Desupported Features

No feature has been desupported in Oracle Database 10g release 2 (10.2). That is, Oracle Database 10g release 2 (10.2) supports all features that were supported in Oracle Database 10g release 1 (10.1).

### Interface Changes

There are some changes in the `setString`, `setCharacterStream`, `setAsciiStream`, `setBytes`, and `setBinaryStream` methods of `PreparedStatement` in Oracle Database 10g release 2 (10.2).

There are three way to bind data for input:

- Direct binding where the data itself is placed in a bind buffer

- Stream binding where the data is streamed

- LOB binding where a temporary lob is created, the data placed in the LOB using the LOB APIs, and the bytes of the LOB locator are placed in the bind buffer

The three kinds of binding have some differences in performance and have an impact on batching. Direct binding is fast and batching is fine. Stream binding is slower, may require multiple round trips, and turns batching off. LOB binding is very slow and requires many round trips. Batching works, but might be a bad idea. They also have different size limits, depending on the type of the SQL statement.

For SQL parameters, the length of normal parameter types, such as `RAW` and `VARCHAR2`, is fixed by the size of the target column. For PL/SQL parameters, the size is limited to a fixed number of bytes, which is 32512.

The original behavior of the APIs were:

- `setString`: Direct bind of characters

- `setCharacterStream`: Stream bind of characters

- `setAsciiStream`: Stream bind of bytes

- `setBytes`: Direct bind of bytes

- `setBinaryStream`: Stream bind of bytes

In Oracle Database 10g release 2 (10.2), automatic switching between binding modes, based on the data size and on the type of the SQL statement is provided.

setBytes and setBinaryStream

For SQL, direct bind is used for size up to 2000 and stream bind for larger

For PL/SQL direct bind is used for size up to 32512 and lob bind is used for larger.

setString, setCharacterStream, and setAsciiStream

For SQL, direct bind is used up to 32766 Java characters and stream bind is used for larger. This is independent of character set.

For PL/SQL, you must be careful about the byte size of the character data in the database character set or the national character set depending on the setting of the form of use parameter. Direct bind is used for data where the byte length is less than 32512 and LOB bind for larger.

For fixed length character sets, multiply the length of the Java character data by the fixed character size in bytes and compare that to 32512. For variable length character sets, there are three cases based on the Java character length, as follows:

- If character length is less than 32512 divided by the maximum character size, then direct bind is used.

- If character length is greater than 32512 divided by the minimum character size, then LOB bind is used.

- If character length is in between and if the actual length of the converted bytes is less than 32512, then direct bind is used, else LOB bind is used.

Note: When a PL/SQL procedure is embedded in a SQL statement, the binding action is different. Refer to "Data Interface for LOBs" for more information.

## Feature List

Table 1-2 lists the features and the versions in which they were first supported for each of the three Oracle JDBC drivers: server-side internal driver, JDBC OCI driver, and JDBC Thin driver.

Table 1-2 Feature List

Feature | Server-Side Internal | JDBC OCI | JDBC Thin
--- | --- | --- | ---
JDK 1.0 |  | 7.2.2 | 7.2.2
JDBC 1.0.2 |  | 7.2.2 | 7.2.2
JDK 1.1.1 |  | 8.0.6 | 8.0.6
JDBC 1.22 (No new features; just minor revisions) |  | 8.0.6 | 8.0.6
defineColumnType |  | 8.0.6 | 8.0.6
Row Prefetch |  | 8.0.6 | 8.0.6
Oracle Batching |  | 8.0.6 | 8.0.6
JNI Native Interface |  | 8.1.6 |
JDK 1.2 | 9.0.1 | 8.1.6 | 8.1.6
JDBC 2.0 SQL3 Types (`BLOB`, `CLOB`, `Struct`, `Array`, `REF`) | 8.1.5 | 8.1.5 | 8.1.5
Native LOB |  | 8.1.6 | 9.2.0
Index-By Tables | 10.2.0 | 8.1.6 | 10.1.0
JDBC 2.0 Scrollable ResultSets | 8.1.6 | 8.1.6 | 8.1.6
JDBC 2.0 Updatable ResultSets | 8.1.6 | 8.1.6 | 8.1.6
JDBC 2.0 Standard Batching | 8.1.6 | 8.1.6 | 8.1.6
JDBC 2.0 Connection Pooling | NA | 8.1.6 | 8.1.6
JDBC 2.0 XA | 8.1.6 | 8.1.6 | 8.1.6
Server-side Thin driver | 8.1.6 | NA | NA
JDBC 2.0 RowSets |  | 9.0.1 | 9.0.1
Implicit Statement Caching | 8.1.7 | 8.1.7 | 8.1.7
Explicit Statement Caching | 8.1.7 | 8.1.7 | 8.1.7
Temporary LOBs | 9.0.1 | 9.0.1 | 9.0.1
Object Type Inheritance | 9.0.1 | 9.0.1 | 9.0.1
Multilevel Collections | 9.0.1 | 9.0.1 | 9.0.1
oracle.jdbc Interfaces | 9.0.1 | 9.0.1 | 9.0.1
Native XA |  | 9.0.1 | 10.1.0
OCI Connection Pooling | NA | 9.0.1 | NA
TAF | NA | 9.0.1 | NA
NLS Support | 9.0.1 | 9.0.1 | 9.0.1
JDK 1.3 | 9.2.0 | 9.2.0 | 9.2.0
JDK 1.4 | 10.1.0 | 9.2.0 | 9.2.0
JDBC 3.0 Savepoints | 9.2.0 | 9.2.0 | 9.2.0
New Statement Caching API | 9.2.0 | 9.2.0 | 9.2.0
ConnectionCacheImpl connection cache | NA | 8.1.7 | 8.1.7
Implicit Connection Cache | NA | 10.1.0 | 10.1.0
Fast Connection Failover |  | 10.1.0.3 | 10.1.0.3
Connection Wrapping |  | 9.2.0 | 9.2.0
DMS |  | 9.2.0 | 9.2.0
Service Names in URLs |  | 9.2.0 | 10.2.0
JDBC 3.0 Connection Pooling Properties | NA | 10.1.0 | 10.1.0
JDBC 3.0 Updatable BLOB, CLOB, REF | 10.1.0 | 10.1.0 | 10.1.0
JDBC 3.0 Multiple Open ResultSets | 10.1.0 | 10.1.0 | 10.1.0
JDBC 3.0 Parameter Metadata | 10.1.0 | 10.1.0 | 10.1.0
JDBC 3.0 Set/Get Stored Procedures Parameters by Name | 10.1.0 | 10.1.0 | 10.1.0
JDBC 3.0 Statement Pooling | 10.1.0 | 10.1.0 | 10.1.0
Set Statement Parameters By Name | 10.1.0 | 10.1.0 | 10.1.0
End-to-End Tracing |  | 10.1.0 | 10.1.0
Web RowSet |  | 10.1.0 | 10.1.0
JDK 5.0 |  | 10.1.0 | 10.1.0
Proxy Authentication |  | 10.2.0 | 10.1.0
JDBC 3.0 Auto Generated Keys |  | 10.2.0 | 10.2.0
JDBC 3.0 Holdable Cursors | 10.2.0 | 10.2.0 | 10.2.0
JDBC 3.0 Local/Global Transaction Switching | 9.2.0 | 9.2.0 | 9.2.0
Run-time Connection Load Balancing | NA | 10.2.0 | 10.2.0
Extended `set` `XXX` and `get` `XXX` for LOBs |  | 10.2.0 | 10.2.0
XA Connection Cache | NA | 10.2.0 | 10.2.0
DML Returning |  | 10.2.0 | 10.2.0
JSR 114 RowSets |  | 10.2.0 | 10.2.0
SSL Encryption |  | 9.2.0 | 10.2.0
SSL Authentication |  | 9.2.0 |
Radius Authentication |  | 10.2.0 |

Notes: In the table, NA implies that the feature is not applicable for the corresponding Oracle JDBC driver. The `ConnectionCacheImpl` connection cache feature is deprecated in Oracle Database 10g. Implicit Connection Cache replaces this in Oracle Database 10g.